DROP TABLE tb_ScoreImport

CREATE TABLE tb_ScoreImport(
	PlayerId INT NOT NULL,
	CourseId INT NOT NULL,
	Date SMALLDATETIME NOT NULL,
	H1 INT NOT NULL,
	H2 INT NOT NULL,
	H3 INT NOT NULL,
	H4 INT NOT NULL,
	H5 INT NOT NULL,
	H6 INT NOT NULL,
	H7 INT NOT NULL,
	H8 INT NOT NULL,
	H9 INT NOT NULL,
	CONSTRAINT PK_ScoreImport PRIMARY KEY(PlayerId, CourseId, Date),
	CONSTRAINT FK_ScoreImport_Player FOREIGN KEY (PlayerId)
		REFERENCES tb_Player (PlayerId),
	CONSTRAINT FK_ScoreImport_Course FOREIGN KEY (CourseId)
		REFERENCES tb_Course (CourseId))

BULK INSERT tb_ScoreImport
FROM		'G:\Projects\Golf\sql\Data\Scorecard.txt'
WITH(		CHECK_CONSTRAINTS,
			FIELDTERMINATOR = '|',
			ROWTERMINATOR = '|\n')

DELETE tb_Score
DELETE tb_Scorecard

INSERT INTO tb_Scorecard(
	PlayerId,
	CourseId,
	Date)
SELECT
	PlayerId,
	CourseId,
	Date
FROM
	tb_ScoreImport

INSERT tb_Score(
	ScorecardId,
	HoleId,
	Strokes)
SELECT
	SC.ScorecardId, SCI.HoleId, SCI.Strokes
FROM
	tb_Scorecard SC
INNER JOIN (
	SELECT PlayerId, SI.CourseId, Date, 1 AS 'HoleId', H1 AS 'Strokes'
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 1
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 2, H2
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 2
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 3, H3
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 3
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 4, H4
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 4
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 5, H5
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 5
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 6, H6
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 6
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 7, H7
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 7
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 8, H8
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 8
	UNION ALL SELECT PlayerId, SI.CourseId, Date, 9, H9
	FROM tb_ScoreImport SI INNER JOIN tb_Hole H ON
	SI.CourseId = H.CourseId AND H.Ordinal = 9 ) SCI
ON
	SC.PlayerId = SCI.PlayerId
AND
	SC.CourseId = SCI.CourseId
AND
	SC.Date = SCI.Date

DROP TABLE tb_ScoreImport